package com.ele.Dao.DaoImpl;

import cn.hutool.db.Db;
import cn.hutool.db.handler.BeanHandler;
import com.ele.Dao.EmployeeDao;
import com.ele.Pojo.Employee;
import com.ele.Utils.JDBCUtils;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class EmployDaoImp implements EmployeeDao {
    /**
     * 查询所有
     */
    @Override
    public List<Employee> getAll() throws SQLException {
        Connection conn =JDBCUtils.getConn();
        String sql ="select * from employee";
        PreparedStatement stmt =conn.prepareStatement(sql);
        ResultSet x =stmt.executeQuery();
        List<Employee> employees = new ArrayList<>();
        while (x.next()){
            Employee employee =new Employee();
            employee.setId(x.getInt("id"));
            employee.setAccount(x.getString("account"));
            employee.setPassword(x.getString("password"));
            employee.setNickname(x.getString("nickname"));
            employee.setTelephone(x.getString("telephone"));
            employee.setRole(x.getString("role"));
            employee.setIsDel(x.getInt("is_del"));
            employees.add(employee);

        }
        stmt.close();
        conn.close();
        return employees;
    }

    /**
     * 根据条件查
     * @param account
     * @param telephone
     * @param nickname
     * @return
     */
    @Override
    public List<Employee> query(String account, String telephone, String nickname) throws SQLException {
       Connection conn =JDBCUtils.getConn();
       String sql ="select * from employee where account like ? or telephone like ? or nickname like ?";
       PreparedStatement stmt =conn.prepareStatement(sql);
       stmt.setString(1,"%"+account+"%");
        stmt.setString(2,"%"+telephone+"%");
        stmt.setString(3,"%"+nickname+"%");
        ResultSet x =stmt.executeQuery();
        List<Employee> employees = new ArrayList<>();
        while (x.next()){
            Employee employee =new Employee();
            employee.setId(x.getInt("id"));
            employee.setAccount(x.getString("account"));
            employee.setPassword(x.getString("password"));
            employee.setNickname(x.getString("nickname"));
            employee.setTelephone(x.getString("telephone"));
            employee.setRole(x.getString("role"));
            employee.setIsDel(x.getInt("is_del"));
            employees.add(employee);

        }
        stmt.close();
        conn.close();
        return employees;

    }

    /**
     * 添加员工
     */
    public Integer addEmp(Employee emp) throws SQLException {
        Connection conn =JDBCUtils.getConn();
        String sql ="insert into employee (id,account,password,nickname,telephone,role,is_del) values (null,?,?,?,?,?,?)";
//        System.out.println("账号"+emp.getAccount());
//        System.out.println("号"+emp.getTelephone());
//        System.out.println("昵称"+emp.getNickname());
//        System.out.println("密码"+emp.getPassword());
//        System.out.println("状态"+emp.getRole());;
        PreparedStatement stmt =conn.prepareStatement(sql);
        stmt.setString(1,emp.getAccount());
        stmt.setString(2,emp.getPassword());
        stmt.setString(3,emp.getNickname());
        stmt.setString(4,emp.getTelephone());
        stmt.setString(5,emp.getRole());
        stmt.setInt(6,emp.getIsDel());
        int x=stmt.executeUpdate();
        stmt.close();
        conn.close();
        return x;
    }

    /**
     * 修改
     * @param emp
     * @return
     * @throws SQLException
     */
    @Override
    public Integer update(Employee emp) throws SQLException {
        Connection conn=JDBCUtils.getConn();
        String sql = "update employee set account = ? , password = ? , nickname = ? , telephone = ?, role = ?, is_del = ? where id = ?";
        PreparedStatement stmt =conn.prepareStatement(sql);
        stmt.setString(1,emp.getAccount());
        stmt.setString(2,emp.getPassword());
        stmt.setString(3,emp.getNickname());
        stmt.setString(4,emp.getTelephone());
        stmt.setString(5,emp.getRole());
        stmt.setInt(6,emp.getIsDel());
        stmt.setInt(7,emp.getId());
        int x=stmt.executeUpdate();
        stmt.close();
        conn.close();
        return x;
    }

    /**
     * 删除
     * @param id
     * @return
     * @throws SQLException
     */
    @Override
    public Integer delete(String id) throws SQLException {
        Connection conn=JDBCUtils.getConn();

        String sql ="delete  from employee where id=?";
        PreparedStatement stmt = conn.prepareStatement(sql);
        stmt.setString(1,id);
        //System.out.println(id);
        int x =stmt.executeUpdate();
        stmt.close();
        conn.close();
        return x;
    }

    @Override
    public Employee empLogin(String account, String password) throws SQLException {
        DataSource datasource = JDBCUtils.getDs();
        return Db.use(datasource).query("select * from employee where account = ? and password = ?" , BeanHandler.create(Employee.class),account,password);
    }

}
